#!/usr/bin/python3
from flask import Flask,request,jsonify,send_file,render_template,make_response ,redirect# 导入Flask核心模块
import pymysql,json
from bp.chuku import bp as chuku_bp
from sql import MySql
from flask_cors import CORS
from pymysql import MySQLError
 
app = Flask(__name__)  # 创建应用实例
app.register_blueprint(chuku_bp)

CORS(app)

@app.route('/')   # 定义根路由 
def index():
    if not request.cookies.get("username"):
        return redirect("/login")
    stu_list = (
        {"id":1,"name":"baocongqin","age":18,"gender":"man"},
        {"id":2,"name":"zs","age":28,"gender":"man"},
        {"id":3,"name":"刘备","age":38,"gender":"man"},
        {"id":4,"name":"张飞","age":48,"gender":"man"},
        {"id":5,"name":"李云龙","age":58,"gender":"man"},
   
        )
    return render_template("index.html",mytitle="hello wqass",rows=stu_list)


 
@app.route('/login',methods=["GET","POST"])   # 定义根路由 
def login():
    if request.method == "GET":
            return render_template("login.html")
    elif request.method == 'POST':
        jsonParam = request.json
        print(jsonParam)
        username = jsonParam["username"]
        password = jsonParam["password"]
        if username == 'baocongqin' and password == '123456':
            resp = make_response({"code":1,"message":"succefully login"})
            resp.set_cookie("username","baocongqin",max_age=60*10) #ten minites
            return resp
        else:
            return jsonify({"code":0})
 
@app.route('/favicon.ico')   # 定义根路由 
def favicon():
    return send_file("static/favicon.ico") 

@app.route('/set_cookie')   # 定义根路由 
def set_cookie():
    resp = make_response("Cookies have been set successfully!")
    resp.set_cookie("username","zs",max_age=60*5) # five minites active
    resp.set_cookie("password","123456")
    return resp



@app.route('/getrow/<dbname>/<tbname>/<int:id>')   # 定义抽象的路由 访问表格中的数据
def get_row(dbname,tbname,id):
    MySql.cursor.execute(f"select * from {dbname}.{tbname} where id = {id};")
    row = MySql.cursor.fetchone()
    if row: # 检查row是否为空
        return render_template("index.html",mytitle="hello wq",rows=(row,))
    else:
        return f"不存在该记录 id={id}"
    

@app.route('/fetchall/<dbname>/<tbname>')   # 定义抽象的路由 访问表格中的数据
def fetchAll(dbname,tbname):
    MySql.cursor.execute(f"select * from {dbname}.{tbname};")
    rows = MySql.cursor.fetchall()
    return jsonify(rows)    


@app.route('/saveall/<dbname>/<tbname>',methods=['POST'])   # 定义抽象的路由 访问表格中的数据
def saveAll(dbname,tbname):
    # 获取参数
    jsonParam = request.json # json不带括号 json为属性
    rows = jsonParam["rows"]
    print(jsonParam)
    # jsonParam参数格式如下 {"fields":[id,name,sex...],"rows":[row1,row2,.....]}
    # make sql statement
    strFields = "("
    strValues = "("
    for field in jsonParam["fields"]:
        strFields += f"{field},"
        strValues += f"%({field})s,"
    # 去年最后一个逗号 并补上)
    strFields=strFields.rstrip(",")+")"
    strValues=strValues.rstrip(",")+")"
    
    sql = f"insert into {dbname}.{tbname} {strFields} values{strValues};"
    print(sql)

    result = {"code":1}
    # 保存至数据库
    try:
        # 使用executemany插入多条数据
        MySql.cursor.executemany(sql,rows)
        MySql.conn.commit()
    
    except MySQLError as e:
         # 发生错误时回滚
        MySql.conn.rollback()
        result["code"] = 0
        result["message"] = e
        print(f"数据库错误: {e}")
    finally:
        return jsonify(result)    


@app.route('/pagination/<dbname>/<tbname>',methods=['POST'])   # 定义抽象的路由 访问表格中的数据
def pagination(dbname,tbname):
    # 获取参数
    jsonParam = request.json
    result = {"code":1,"message":"successfully fetched data"}
    # 获取记录总数
    sql = f"select   count(*) as total from {dbname}.{tbname} where name like '%{jsonParam["searchString"]}%';"
    MySql.cursor.execute(sql)
    row = MySql.cursor.fetchone()
    result["total"] = row["total"]
    # 获取记录数据
    currentPage = int(jsonParam["currentPage"])-1 #从零记数
    pageSize = int(jsonParam["pageSize"])
    iStart = currentPage*pageSize
    sql = f"select   * from {dbname}.{tbname} where name like '%{jsonParam["searchString"]}%' limit {iStart},{pageSize};"
    print(sql)
    MySql.cursor.execute(sql)
    rows = MySql.cursor.fetchall()
    result["rows"] = rows
    return jsonify(result)    



@app.route('/api/test',methods=['POST'])   # 测试用的api接口
def test():
    # 获取参数
    jsonParam = request.json
    print(jsonParam)
    result = {"code":1,"message":"successfully fetched data","data":jsonParam}   
    return jsonify(result)    



if __name__ == '__main__':
    
    # 连接服务器
    MySql.conn = pymysql.connect(
    host="127.0.0.1",
    user="admin",
    password="1",
    cursorclass=pymysql.cursors.DictCursor,
    # isolation_level=2
    )

# 获取cursor
    MySql.cursor = MySql.conn.cursor()
    app.run(host='0.0.0.0',  port=5000, debug=True)  # 启动开发服务器
